

#include "../include/client.h"
#include "../include/type.h"

#define DATABASE "mydatabase"

#define TABLE "temp"

static int get_recode_count_callback(void *data, int argc, char **argv, char **azColName);
static int get_recode_callback(void *ptr, int argc, char **argv, char **azColName);

/*
int main()
{
    pack_t pp;

    int num = 0;

    sqlite3 *db;

    datbas_open(&db);

    //datbas_insert(db,"12","13","14","15");

    num = get_recode_count(db);
    printf("Recode count=%d\n",num);

    get_recode(db,&pp);

    printf("get date=%s time=%s sn=%s temp=%s\n",pp.s_pack.date,pp.s_pack.time,pp.s_pack.sn,pp.s_pack.temp);

  //  delete_recode(db,pp.id,0);//删除指定行
  delete_recode(db,5,1);//删除前5行

    num=get_recode_count(db);
    printf("delete after Recode count=%d\n",num);

    datbas_close(db);

    return 0;
}
*/


/*func:If the database does not exist, the database is created, 
       and if the table does not exist, the table is created.
  para:
       db:Returns the handle to the database operation.
  return:
       success:0
       fail:-1
*/
int datbas_open(sqlite3 **db)
{
	char        *errmsg;

	if(sqlite3_open(DATABASE,db)!=SQLITE_OK)//创建或打开数据库
	{

		printf("sqlite3_open error:%s\n",sqlite3_errmsg(*db));
        return -1;
	}

	printf("Create database success.\n");

	//判断数据库中的表是否存在，如果不存在则创建表
	if(sqlite3_exec(*db,"create table if not exists "TABLE" (id INTEGER PRIMARY KEY AUTOINCREMENT,date varchar,time varchar,sn varchar,temp varchar);",NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("Create failed:%s\n",errmsg);
        return -1;
	}
    printf("Open table "TABLE" successfully!\n");

	return 0;
}


/*func:Insert data into the database.
  para:
       db:the handle to the database operation.
       date:Date fields
       time:Time fields
       sn:Serial number
       temp:temperature fields
  return:
       success:0
       fail:-1
*/
int datbas_insert(sqlite3 *db,char *date,char *time,char *sn,char *temp)
{
	char        *errmsg;

	char        sql[128];

	if(!db)
	{
		printf("Invalid input argument db!\n");
		return -1;
	}

	memset(sql,0,sizeof(sql)); 
	if(snprintf(sql, sizeof(sql), "insert into "TABLE" values(null,'%s','%s','%s','%s');",date,time,sn,temp)<0)
	{
		printf("sprintf error:%s\n",strerror(errno));
		return -1;
	}

	printf("sql: [%s]\n",sql);

	if((sqlite3_exec(db,sql,NULL,NULL,&errmsg)) != SQLITE_OK)
	{ 
		printf("insert data into database failed:%s\n",errmsg);
		return -1;
	}
	printf("insert data into database successfully\n");     

	return 0;
}

/*功能:关闭数据库
 *参数:db:数据库操作句柄
 *返回值:成功返回0,失败返回-1
 * */
int datbas_close(sqlite3 *db)
{

	if(sqlite3_close(db)!=SQLITE_OK)
	{
		printf("close database error:%s\n",sqlite3_errmsg(db));
		return -1;
	}

	return 0;

}

/*功能:查询数据库中是否有历史数据
 *参数:db:数据库操作句柄
 *     num:表中记录的总条数
 *返回值:成功返回记录数,失败返回-1
 **/

int get_recode_count(sqlite3 *db)
{
    char            *sql;
    char            *zErrMsg = 0;
    int             rc = -1;
    int             num = 0;

    //  Create SQL statement 
    sql = "SELECT count(*) from "TABLE"";

    //Execute SQL statement 
    rc = sqlite3_exec(db, sql, get_recode_count_callback, (void*)&num, &zErrMsg);
    if( rc != SQLITE_OK )
    {
        printf("SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        return -1;
    }
    else
    {
        if(num == 0)
        {
            sql = "UPDATE sqlite_sequence SET seq=0 WHERE name = '"TABLE"';";
            rc = sqlite3_exec(db, sql, NULL, NULL, NULL);
            if(rc != SQLITE_OK)
            {
                printf("sep error\n");
            }
        }
        printf("Get recode count operation done successfully\n");
    }
    return num;
}

/*功能：获取记录总数的回调函数
 *
 * */
static int get_recode_count_callback(void *data, int argc, char **argv, char **azColName)
{

    int *count = (int *)data;
    if(argv[0] != NULL)
    {
        *count = atoi(argv[0]);
    }
    else
    {
        *count = 0;
    }
    return 0;
}

/*功能：从数据库表中取出一条数据
 *参数：db:数据库操作句柄
 *      pp:返回表中的记录
 *返回值：成功返回0，失败返回-1*/
int get_recode(sqlite3 *db,pack_t *pp)
{
    char            *sql;
    char            *zErrMsg = 0;
    int             rc = -1;

    sql = "select * from "TABLE" LIMIT 1;";

    rc = sqlite3_exec(db, sql, get_recode_callback,(void *)pp, &zErrMsg);
    if( rc != SQLITE_OK )
    {
        printf("SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        return -1;
    }
    else
    {
        printf("Get recode operation done successfully\n");
    }
    return 0;
}

/*功能：获取记录的回调函数*/
static int get_recode_callback(void *ptr, int argc, char **argv, char **azColName)
{
    int i;

    pack_t *data = (pack_t *)ptr;

    for(i=0; i<argc; i++)
    {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
        if(!strcmp("id",azColName[i]))
        {
            data->s_pack.id = atoi(argv[i]);
        }
        else if(!strcmp("date", azColName[i]))
        {
            strcpy(data->s_pack.date,argv[i]);
        }
        else if(!strcmp("time",azColName[i]))
        {
            strcpy(data->s_pack.time,argv[i]);
        }
        else if(!strcmp("sn",azColName[i]))
        {
            strcpy(data->s_pack.sn,argv[i]);
        }
        else if(!strcmp("temp",azColName[i]))
        {
            strcpy(data->s_pack.temp,argv[i]);
        }
        
    }
    printf("\n");
    return 0;
}



/*功能:删除记录
 *参数:db:数据库操作句柄
 *     cmd:0删除指定行,1删除前n行
 *     id:若cmd为0,id作为指定行，否则为前n行
 *返回:成功返回0,失败返回-1*/
int delete_recode(sqlite3 *db,int id,int cmd)
{
    int rc = -1;
    char *zErrMsg = 0;
    char sql[200];

    if(cmd)
    {
        sprintf(sql,"delete from "TABLE" where id in(select id from "TABLE" limit %d);",id);
    }

    else
    {
        sprintf(sql,"DELETE from "TABLE" where id=%d;",id);
    }
   
    rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
    if( rc != SQLITE_OK )
    {
        printf("SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        return -1;
    }
    else
    {
        printf("Delete operation done successfully\n");
    }

    return 0;
}



